﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Excel2DbTool.Helpers
{
	public sealed class DbTableNames
	{
		public static readonly String BDC_XM = "BDC_XM";
		public static readonly String BDC_ZRZ = "BDC_ZRZ";
		public static readonly String BDC_LJZ = "BDC_LJZ";
		public static readonly String BDC_C = "BDC_C";
		public static readonly String BDC_H = "BDC_H";


		private DbTableNames()
		{
		}
	}

	public sealed class ProjectZDMappingProvider
	{
		private static IEnumerable<ProjectZDMapping> projectZDMappings = new List<ProjectZDMapping>(20)
		{
			new ProjectZDMapping("150523100033GB00001",new List<string>(){"东岸春城小区4#、6#、8#、9#","东岸春城小区7#楼"}),
			new ProjectZDMapping("150523100011GB00527",new List<string>(){"富贵园小区1期"}),
			new ProjectZDMapping("150523100012GB00257",new List<string>(){"恒通花园","","恒通花园1#、9#","恒通花园4#","恒通花园5#"}),
			new ProjectZDMapping("150523100013GB00102",new List<string>(){"锦绣家园小区","","锦绣家园住宅小区"}),
			new ProjectZDMapping("150523100027GB00112",new List<string>(){"开鲁县和谐家园1#、2#、3#、4#商业住宅小区","开鲁县和谐家园厢房楼","开鲁县和谐小区5#楼"}),
			new ProjectZDMapping("150523100035GB20424",new List<string>(){"开鲁县吉祥园小区"}),
			new ProjectZDMapping("150523100017GB00131",new List<string>(){"开鲁县清华园小区"}),
			new ProjectZDMapping("150523100011GB00522",new List<string>(){"开鲁县旺福家园"}),
			new ProjectZDMapping("150523100010GB00028",new List<string>(){"开鲁县馨港佳源商业住宅小区"}),
			new ProjectZDMapping("150523100013GB00101",new List<string>(){"开鲁县阳光家园小区"}),
			new ProjectZDMapping("150523100021GB00151",new List<string>(){"隆胜小区1#商住楼","隆胜小区2＃住宅楼"}),
			new ProjectZDMapping("150523100021GB00152",new List<string>(){"隆胜小区二期"}),
			new ProjectZDMapping("150523100016GB00049",new List<string>(){"牡丹园小区"}),
			new ProjectZDMapping("150523100009GB00328",new List<string>(){"盛达小区二期"}),
			new ProjectZDMapping("150523100029GB00394",new List<string>(){"盛世家园"}),
			new ProjectZDMapping("150523100032GB00224",new List<string>(){"天怡佳苑","天怡佳苑1#、2#、3#楼","天怡佳苑4#楼"}),
			new ProjectZDMapping("150523100018GB00154",new List<string>(){"万合永嘉园小区"}),
			new ProjectZDMapping("150523100022GB00505",new List<string>(){"长青小区二期5#楼","长青小区二期6#楼","长青小区二期7#楼","长青小区二期8#楼"}),
		};


		public static string GetMatchZDNumber(string projectName)
		{
			foreach (var mapping in projectZDMappings)
			{
				if (mapping.IsMatchCurrentProject(projectName))
				{
					return mapping.ZDNumber;
				}
			}

			return string.Empty;
		}


		class ProjectZDMapping
		{
			private readonly IEnumerable<string> projects;


			public ProjectZDMapping(string zdNumber, IEnumerable<string> projects)
			{
				ZDNumber = zdNumber;
				this.projects = projects;
			}

			/// <summary>
			/// 宗地号
			/// </summary>
			public string ZDNumber { get; private set; }

			public bool IsMatchCurrentProject(string project)
			{
				var matcher = projects.FirstOrDefault(projectName => string.Equals(project, projectName, StringComparison.OrdinalIgnoreCase));
				return matcher != null;
			}
		}
	}

	public static class OrginTableFieldNames
	{
		#region Field Name
		private static readonly List<String> fieldNames = new List<string>()
		{
			"序号",
			"LICENSENO",
			"LANDCOMPRESS",
			"PROJECTNAME",
			"XQMC",
			"BZ",
			"PRODIST",
			"PROADDR",
			"PLANTYPE",
			"SUMBUILDAREA",
			"SUMLANDAREA",
			"HOUSETYPE",
			"SALEROOMTEL",
			"STRUCTURE",
			"SALEAREA",
			"SALEBUILDER",
			"SALEHOUSE",
			"TOPPRICE",
			"LOWPRICE",
			"PROFULLLAYER",
			"TOTALCONAREA",
			"CORPACCOUNTS",
			"CORPNAME",
			"REGZONE",
			"REGADDRESS",
			"CORPZONE",
			"CORPADDRESS",
			"CORPORATION",
			"CONTACT",
			"CORPTYPE",
			"MOBILETELEPHONE",
			"TELEPHONE",
			"EMAIL",
			"FAXES",
			"QUALIFICATORYLEVEL",
			"ZIPCODE",
			"REGISTEREDCAPITAL",
			"HOUSEID",
			"BUILDNO",
			"BUILDINGNO",
			"HOUSENO",
			"FULLLAYER",
			"HOUSEDIST",
			"HOUSEADDR",
			"HOUSEDIRECTION",
			"HOUSEFACILITIES",
			"TRADECATGORY",
			"SOURCE",
			"LAYER",
			"BUILDAREA",
			"INTERAREA",
			"PUBLICAREA",
			"NOTSETAREA",
			"BUILDTYPE",
			"MMPRICE",
			"MM_TOTALPRICE",
			"SAVEDATE",
			"USE",
			"ISCOMMERCE",
			"LICSTATE",
			"SELLSTATE",
			"ISBACKUP",
			"EXPECTMORTSTATE",
			"SELLEDSEALSTATE",
			"COURSEMORTSTATE",
			"COURSESEALSTATE",
			"FSELLSTATE",
			"ISCLOSE",
			"ISBOOK",
			"REALFILEID",
			"NOWMORTSTATE",
			"NOWSEALSTATE",
			"BUSINESSFILEID",
			"BOOKCOUNT",
			"库状态",
			"MODIFYTIME",
			"WAIT_ID",
		};

		/// <summary>
		/// 代表没有与目标数据库中匹配的字段
		/// </summary>
		public static readonly string NONE = null;

		public static readonly string 序号 = fieldNames[0];
		public static readonly string LICENSENO = fieldNames[1];
		public static readonly string LANDCOMPRESS = fieldNames[2];
		public static readonly string PROJECTNAME = fieldNames[3];
		public static readonly string XQMC = fieldNames[4];
		public static readonly string BZ = fieldNames[5];
		public static readonly string PRODIST = fieldNames[6];
		public static readonly string PROADDR = fieldNames[7];
		public static readonly string PLANTYPE = fieldNames[8];
		public static readonly string SUMBUILDAREA = fieldNames[9];
		public static readonly string SUMLANDAREA = fieldNames[10];
		public static readonly string HOUSETYPE = fieldNames[11];
		public static readonly string SALEROOMTEL = fieldNames[12];
		public static readonly string STRUCTURE = fieldNames[13];
		public static readonly string SALEAREA = fieldNames[14];
		public static readonly string SALEBUILDER = fieldNames[15];
		public static readonly string SALEHOUSE = fieldNames[16];
		public static readonly string TOPPRICE = fieldNames[17];
		public static readonly string LOWPRICE = fieldNames[18];
		public static readonly string PROFULLLAYER = fieldNames[19];
		public static readonly string TOTALCONAREA = fieldNames[20];
		public static readonly string CORPACCOUNTS = fieldNames[21];
		public static readonly string CORPNAME = fieldNames[22];
		public static readonly string REGZONE = fieldNames[23];
		public static readonly string REGADDRESS = fieldNames[24];
		public static readonly string CORPZONE = fieldNames[25];
		public static readonly string CORPADDRESS = fieldNames[26];
		public static readonly string CORPORATION = fieldNames[27];
		public static readonly string CONTACT = fieldNames[28];
		public static readonly string CORPTYPE = fieldNames[29];
		public static readonly string MOBILETELEPHONE = fieldNames[30];
		public static readonly string TELEPHONE = fieldNames[31];
		public static readonly string EMAIL = fieldNames[32];
		public static readonly string FAXES = fieldNames[33];
		public static readonly string QUALIFICATORYLEVEL = fieldNames[34];
		public static readonly string ZIPCODE = fieldNames[35];
		public static readonly string REGISTEREDCAPITAL = fieldNames[36];
		public static readonly string HOUSEID = fieldNames[37];
		public static readonly string BUILDNO = fieldNames[38];
		public static readonly string BUILDINGNO = fieldNames[39];
		public static readonly string HOUSENO = fieldNames[40];
		public static readonly string FULLLAYER = fieldNames[41];
		public static readonly string HOUSEDIST = fieldNames[42];
		public static readonly string HOUSEADDR = fieldNames[43];
		public static readonly string HOUSEDIRECTION = fieldNames[44];
		public static readonly string HOUSEFACILITIES = fieldNames[45];
		public static readonly string TRADECATGORY = fieldNames[46];
		public static readonly string SOURCE = fieldNames[47];
		public static readonly string LAYER = fieldNames[48];
		public static readonly string BUILDAREA = fieldNames[49];
		public static readonly string INTERAREA = fieldNames[50];
		public static readonly string PUBLICAREA = fieldNames[51];
		public static readonly string NOTSETAREA = fieldNames[52];
		public static readonly string BUILDTYPE = fieldNames[53];
		public static readonly string MMPRICE = fieldNames[54];
		public static readonly string MM_TOTALPRICE = fieldNames[55];
		public static readonly string SAVEDATE = fieldNames[56];
		public static readonly string USE = fieldNames[57];
		public static readonly string ISCOMMERCE = fieldNames[58];
		public static readonly string LICSTATE = fieldNames[59];
		public static readonly string SELLSTATE = fieldNames[60];
		public static readonly string ISBACKUP = fieldNames[61];
		public static readonly string EXPECTMORTSTATE = fieldNames[62];
		public static readonly string SELLEDSEALSTATE = fieldNames[63];
		public static readonly string COURSEMORTSTATE = fieldNames[64];
		public static readonly string COURSESEALSTATE = fieldNames[65];
		public static readonly string FSELLSTATE = fieldNames[66];
		public static readonly string ISCLOSE = fieldNames[67];
		public static readonly string ISBOOK = fieldNames[68];
		public static readonly string REALFILEID = fieldNames[69];
		public static readonly string NOWMORTSTATE = fieldNames[70];
		public static readonly string NOWSEALSTATE = fieldNames[71];
		public static readonly string BUSINESSFILEID = fieldNames[72];
		public static readonly string BOOKCOUNT = fieldNames[73];
		public static readonly string 库状态 = fieldNames[74];
		public static readonly string MODIFYTIME = fieldNames[75];
		public static readonly string WAIT_ID = fieldNames[76];

		/* Powershell script
		 
			$content = Get-Content "C:\Users\LQ\Desktop\d.txt";
			$lst = $content.Split();
			$str = "";
			for ($i = 0; $i -lt $lst.Count; $i++)
			{ 
			 $str += [String]::Format('"{0}",',$lst[$i])+[System.Environment]::NewLine;
			}
			$str >> "C:\Users\LQ\Desktop\result.txt"       
		 
		 */

		#endregion


		public static string GetDropTableSqlStr()
		{
			return string.Format("DROP TABLE {0}", ConfigHelper.TableName);
			//return GetTheSqlStrMakeSureTempTableNotExist();
		}

		static string GetTheSqlStrMakeSureTempTableNotExist()
		{
			return string.Format("declare   num   number;   begin         select count(1) into num from all_tables where TABLE_NAME = '{0}' and OWNER='SCOTT';         if   num=1   then             execute immediate 'drop table {0}';         end   if;   end; ", ConfigHelper.TableName);
		}

		public static string GetCreateTableSqlStr()
		{
			StringBuilder sb = new StringBuilder(1500);

			sb.AppendFormat("CREATE TABLE {0} ({1})", ConfigHelper.TableName, GetAllFieldDefineStr());
			return sb.ToString();
		}


		static string GetAllFieldDefineStr()
		{
			StringBuilder sb = new StringBuilder(1000);

			int count = fieldNames.Count;
			for (int i = 0; i < count; i++)
			{
				sb.AppendFormat("{0} VARCHAR2(220){1}", fieldNames[i], i == count - 1 ? "" : ",");
			}

			return sb.ToString();
		}
	}


	#region Backup
	/*
	public static class OrginTableFieldNames
	{
		#region Field Name
		private static readonly List<String> fieldNames = new List<string>()
		{
			"FILEID",
			"CONTROLID",
			"STEPID",
			"LICENSENO",
			"LANDCOMPRESS",
			"PROJECTNAME",
			"PRODIST",
			"PROADDR",
			"PLANTYPE",
			"SUMBUILDAREA",
			"SUMLANDAREA",
			"HOUSETYPE",
			"SALEROOMTEL",
			"STRUCTURE",
			"SALEAREA",
			"SALEBUILDER",
			"SALEHOUSE",
			"TOPPRICE",
			"LOWPRICE",
			"PROFULLLAYER",
			"LANDBRANCHNO",
			"LANDPARENTNO",
			"LANDSONNO",
			"PROPERTY",
			"USEAREA",
			"BASEAREA",
			"GRAPHICNO",
			"RENTCOST",
			"PLANTCERNO",
			"PLANTLEVEL",
			"EASTTO",
			"SOUTHTO",
			"WESTTO",
			"NORTHTO",
			"TOTALCONAREA",
			"VACILITYPER",
			"DENSITYCONS",
			"CORPACCOUNTS",
			"CORPNAME",
			"REGZONE",
			"REGADDRESS",
			"CORPZONE",
			"CORPADDRESS",
			"CORPORATION",
			"LICENSECODE",
			"CONTACT",
			"CORPTYPE",
			"MOBILETELEPHONE",
			"TELEPHONE",
			"EMAIL",
			"FAXES",
			"QUALIFICATORYLEVEL",
			"ZIPCODE",
			"REGISTEREDCAPITAL",
			"HOUSEID",
			"FILE_ID",
			"BUILDNO",
			"BUILDINGNO",
			"HOUSENO",
			"FULLLAYER",
			"HOUSEDIST",
			"HOUSEADDR",
			"HOUSEDIRECTION",
			"HOUSEFACILITIES",
			"TRADECATGORY",
			"SOURCE",
		   "LAYER",
		   "BUILDAREA",
		   "INTERAREA",
		   "PUBLICAREA",
		   "NOTSETAREA",
		   "BUILDTYPE",
		   "MMPRICE",
		   "GRAPHNO",
		   "CONTRCTNO",
		   "MM_TOTALPRICE",
		   "SAVEDATE",
		   "USE",
		   "ISCOMMERCE",
		   "LICSTATE",
		   "SELLSTATE",
		   "ISBACKUP",
		   "EXPECTMORTSTATE",
		   "SELLEDSEALSTATE",
		   "COURSEMORTSTATE",
		   "COURSESEALSTATE",
		   "FSELLSTATE",
		   "ISCLOSE",
		   "ISBOOK",
		   "REALFILEID",
		   "NOWMORTSTATE",
		   "NOWSEALSTATE",
		   "BUSINESSFILEID",
		   "BOOKCOUNT",
		   "AREA_CODE",
		   "老系统办件号",
		   "库状态",
		   "MODIFYTIME",
		   "WAIT_ID",
		};

		/// <summary>
		/// 代表没有与目标数据库中匹配的字段
		/// </summary>
		public static readonly string NONE = null;

		public static readonly string FILEID = fieldNames[0];
		public static readonly string CONTROLID = fieldNames[1];
		public static readonly string STEPID = fieldNames[2];
		public static readonly string LICENSENO = fieldNames[3];
		public static readonly string LANDCOMPRESS = fieldNames[4];
		public static readonly string PROJECTNAME = fieldNames[5];
		public static readonly string PRODIST = fieldNames[6];
		public static readonly string PROADDR = fieldNames[7];
		public static readonly string PLANTYPE = fieldNames[8];
		public static readonly string SUMBUILDAREA = fieldNames[9];
		public static readonly string SUMLANDAREA = fieldNames[10];
		public static readonly string HOUSETYPE = fieldNames[11];
		public static readonly string SALEROOMTEL = fieldNames[12];
		public static readonly string STRUCTURE = fieldNames[13];
		public static readonly string SALEAREA = fieldNames[14];
		public static readonly string SALEBUILDER = fieldNames[15];
		public static readonly string SALEHOUSE = fieldNames[16];
		public static readonly string TOPPRICE = fieldNames[17];
		public static readonly string LOWPRICE = fieldNames[18];
		public static readonly string PROFULLLAYER = fieldNames[19];
		public static readonly string LANDBRANCHNO = fieldNames[20];
		public static readonly string LANDPARENTNO = fieldNames[21];
		public static readonly string LANDSONNO = fieldNames[22];
		public static readonly string PROPERTY = fieldNames[23];
		public static readonly string USEAREA = fieldNames[24];
		public static readonly string BASEAREA = fieldNames[25];
		public static readonly string GRAPHICNO = fieldNames[26];
		public static readonly string RENTCOST = fieldNames[27];
		public static readonly string PLANTCERNO = fieldNames[28];
		public static readonly string PLANTLEVEL = fieldNames[29];
		public static readonly string EASTTO = fieldNames[30];
		public static readonly string SOUTHTO = fieldNames[31];
		public static readonly string WESTTO = fieldNames[32];
		public static readonly string NORTHTO = fieldNames[33];
		public static readonly string TOTALCONAREA = fieldNames[34];
		public static readonly string VACILITYPER = fieldNames[35];
		public static readonly string DENSITYCONS = fieldNames[36];
		public static readonly string CORPACCOUNTS = fieldNames[37];
		public static readonly string CORPNAME = fieldNames[38];
		public static readonly string REGZONE = fieldNames[39];
		public static readonly string REGADDRESS = fieldNames[40];
		public static readonly string CORPZONE = fieldNames[41];
		public static readonly string CORPADDRESS = fieldNames[42];
		public static readonly string CORPORATION = fieldNames[43];
		public static readonly string LICENSECODE = fieldNames[44];
		public static readonly string CONTACT = fieldNames[45];
		public static readonly string CORPTYPE = fieldNames[46];
		public static readonly string MOBILETELEPHONE = fieldNames[47];
		public static readonly string TELEPHONE = fieldNames[48];
		public static readonly string EMAIL = fieldNames[49];
		public static readonly string FAXES = fieldNames[50];
		public static readonly string QUALIFICATORYLEVEL = fieldNames[51];
		public static readonly string ZIPCODE = fieldNames[52];
		public static readonly string REGISTEREDCAPITAL = fieldNames[53];
		public static readonly string HOUSEID = fieldNames[54];
		public static readonly string FILE_ID = fieldNames[55];
		public static readonly string BUILDNO = fieldNames[56];
		public static readonly string BUILDINGNO = fieldNames[57];
		public static readonly string HOUSENO = fieldNames[58];
		public static readonly string FULLLAYER = fieldNames[59];
		public static readonly string HOUSEDIST = fieldNames[60];
		public static readonly string HOUSEADDR = fieldNames[61];
		public static readonly string HOUSEDIRECTION = fieldNames[62];
		public static readonly string HOUSEFACILITIES = fieldNames[63];
		public static readonly string TRADECATGORY = fieldNames[64];
		public static readonly string SOURCE = fieldNames[65];
		/// <summary>
		/// 所在层
		/// </summary>
		public static readonly string LAYER = fieldNames[66];
		public static readonly string BUILDAREA = fieldNames[67];
		public static readonly string INTERAREA = fieldNames[68];
		public static readonly string PUBLICAREA = fieldNames[69];
		public static readonly string NOTSETAREA = fieldNames[70];
		public static readonly string BUILDTYPE = fieldNames[71];
		public static readonly string MMPRICE = fieldNames[72];
		public static readonly string GRAPHNO = fieldNames[73];
		public static readonly string CONTRCTNO = fieldNames[74];
		public static readonly string MM_TOTALPRICE = fieldNames[75];
		public static readonly string SAVEDATE = fieldNames[76];
		public static readonly string USE = fieldNames[77];
		public static readonly string ISCOMMERCE = fieldNames[78];
		public static readonly string LICSTATE = fieldNames[79];
		public static readonly string SELLSTATE = fieldNames[80];
		public static readonly string ISBACKUP = fieldNames[81];
		public static readonly string EXPECTMORTSTATE = fieldNames[82];
		public static readonly string SELLEDSEALSTATE = fieldNames[83];
		public static readonly string COURSEMORTSTATE = fieldNames[84];
		public static readonly string COURSESEALSTATE = fieldNames[85];
		public static readonly string FSELLSTATE = fieldNames[86];
		public static readonly string ISCLOSE = fieldNames[87];
		public static readonly string ISBOOK = fieldNames[88];
		public static readonly string REALFILEID = fieldNames[89];
		public static readonly string NOWMORTSTATE = fieldNames[90];
		public static readonly string NOWSEALSTATE = fieldNames[91];
		public static readonly string BUSINESSFILEID = fieldNames[92];
		public static readonly string BOOKCOUNT = fieldNames[93];
		public static readonly string AREA_CODE = fieldNames[94];
		public static readonly string 老系统办件号 = fieldNames[95];
		public static readonly string 库状态 = fieldNames[96];
		public static readonly string MODIFYTIME = fieldNames[97];
		public static readonly string WAIT_ID = fieldNames[98];

		 //Powershell script
		 
		 //   $content = Get-Content "C:\Users\LQ\Desktop\d.txt";
		 //   $lst = $content.Split();
		 //   $str = "";
		 //   for ($i = 0; $i -lt $lst.Count; $i++)
		 //   { 
		 //       $str += [String]::Format("public static readonly string {0} = fieldNames[{1}];",$lst[$i],$i)+[System.Environment]::NewLine;
		 //   }
		 //   $str >> c:\test.txt         
		 
		

		#endregion


		public static string GetDropTableSqlStr()
		{
			return string.Format("DROP TABLE {0}", ConfigHelper.TableName);
			//return GetTheSqlStrMakeSureTempTableNotExist();
		}

		static string GetTheSqlStrMakeSureTempTableNotExist()
		{
			return string.Format("declare   num   number;   begin         select count(1) into num from all_tables where TABLE_NAME = '{0}' and OWNER='SCOTT';         if   num=1   then             execute immediate 'drop table {0}';         end   if;   end; ", ConfigHelper.TableName);
		}

		public static string GetCreateTableSqlStr()
		{
			StringBuilder sb = new StringBuilder(1500);

			sb.AppendFormat("CREATE TABLE {0} ({1})", ConfigHelper.TableName, GetAllFieldDefineStr());
			return sb.ToString();
		}


		static string GetAllFieldDefineStr()
		{
			StringBuilder sb = new StringBuilder(1000);

			int count = fieldNames.Count;
			for (int i = 0; i < count; i++)
			{
				sb.AppendFormat("{0} VARCHAR2(150){1}", fieldNames[i], i == count - 1 ? "" : ",");
			}

			return sb.ToString();
		}
	}
*/
	#endregion
}
